<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Trainings extends CI_Model {

	function __construct() {
        parent::__construct();
    }

    public function exists($locality, $term, $id) {
    	$sql = "SELECT " .
			   "t.TrainingId AS `Id` " .
			   "FROM " .
			   "trainings AS t " .
			   "WHERE " .
			   "t.Term = " . $term . " AND " .
			   "t.LocalityId = " . $locality;

		if ($id > 0) $sql .= " AND t.TrainingId <> " . $id;

		$query = $this->db->query($sql);

		return ($query->num_rows() > 0);
    }

    public function getPaginateList($page, $rows) {
    	$data = array();

    	$sql = "SELECT " .
			   "t.TrainingId AS `Id`, " .
			   "l.Locality AS `Location`, " .
			   "t.Term, " .
			   "DATE_FORMAT(t.StartDate, '%d-%b-%Y') AS `From`, " .
			   "DATE_FORMAT(t.EndDateProjected, '%d-%b-%Y') AS `Until`, " .
			   "CAST(CASE WHEN t.EndDateActual IS NULL THEN 'On-going' ELSE DATE_FORMAT(t.EndDateActual, '%d-%b-%Y') END AS char(30)) AS `Ended` " .
			   "FROM " .
			   "trainings AS t " .
			   "INNER JOIN localities AS l ON t.LocalityId = l.LocalityId " .
			   "ORDER BY " .
			   "t.Term DESC, " .
			   "t.StartDate DESC " .
			   "LIMIT " .
			   ($page * $rows) - $rows . ", " . $rows;

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$terms = $query->result_array();
			
			foreach ($terms as $term) {
				$trainings = array();

				$sql = "SELECT " .
					   "tu.TrainingUnitId AS `Id`, " . 
					   "tl.TrainingLevel AS `Level`, " .
					   "tu.Remarks " .
					   "FROM " .
					   "trainingunits AS tu " .
					   "INNER JOIN traininglevels AS tl ON tu.TrainingLevelId = tl.TrainingLevelId " .
					   "WHERE " .
					   "tu.TrainingId = " . $term['Id'] . " " .
					   "ORDER BY " .
					   "`Level`";

				$query = $this->db->query($sql);
				if ($query->num_rows() > 0) {
					$levels = $query->result_array();
					$attendees = array();

					foreach ($levels as $level) {
						$sql = "SELECT " .
							   "ta.TrainingAttendeeId AS Id, " .
							   "t.TraineeId, " .
							   "s.FirstName, " .
							   "s.MiddleName, " .
							   "s.LastName, " .
							   "s.Gender, " .
							   "l.Locality, " .
							   "d.District, " .
							   "DATE_FORMAT(ta.StartDate, '%d-%b-%Y') AS `Entered` " .
							   "CAST(CASE WHEN ta.EndDate IS NULL THEN 'Still Enjoying' ELSE DATE_FORMAT(ta.EndDate, '%d-%b-%Y') END AS char(30)) AS `Leaved` " .
							   "ta.IsCompleted, " .
							   "ta.Remarks " .
							   "FROM " .
							   "trainingattendees AS ta " .
							   "INNER JOIN trainees AS t ON ta.TraineeId = t.TraineeId " .
							   "INNER JOIN saints AS s ON t.SaintId = s.SaintId " .
							   "INNER JOIN localities AS l ON l.LocalityId = s.LocalityId " .
							   "INNER JOIN districts AS d ON s.DistrictId = d.DistrictId " .
							   "WHERE " .
							   "ta.TrainingUnitId = " . $level['Id'] . " " .
							   "ORDER BY " . 
							   "s.FirstName, " .
							   "s.MiddleName, " .
							   "s.LastName";

						$query = $this->db->query($sql);
						if ($query->num_rows() > 0) array_push($attendees, $query->result_array());
					}

					$training = array(
						'Id' => $level['Id'],
						'Level' => $level['Level'],
						'Remarks' => $level['Remarks'],
						'Trainees' => $attendees
					);

					array_push($trainings, $training);
				}
				
				$array_push($data, array(
					'Id' => $term['Id'],
					'Location' => $term['Location'],
					'Term' => $term['Term'],
					'From' => $term['From'],
					'Until' => $term['Until'],
					'Ended' => $term['Ended'],
					'Trainings' => $trainings
				));
			} 
		}

		return $data;
    }

    public function recordCount() {
    	$count = 0;

    	$sql = "SELECT " .
			   "COUNT(t.TrainingId) AS `Count` " .
			   "FROM " .
			   "trainings AS t";

		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$row = $query->row_array();
			$count = $row['Count'];
		}

		return $count;
    }

}